<?php
/**********************************************************************************
 
	Checks the last update to the database and sends a message to user
 
 	Copyright (C) 2010 Benjamin Thomas
 
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
 
 	Author: Benjamin Thomas
	Email: thomas.ben@gmail.com
	Created: 07/15/10

 	Calls: None.
 
 **********************************************************************************
 */
  require_once(dirname(__FILE__) . "/../config.php");

 $database = Vars::$database;
 
$conn = mysql_connect('localhost', "root", Vars::$rootpassword);
if (!$conn) { die('Could not connect: ' . mysql_error()); }
$dbcheck = mysql_select_db($database, $conn);

if (!$dbcheck) {
	createDatabase();
	mysql_close($conn);
	exit();
}

// _ONE_ person has code with this version of the code. Screw them, delete their database.
// Depreciate after release of 0.2
$sql = "SHOW TABLES FROM " . $database . " LIKE 'db'";
$rs = mysql_query($sql,$conn);
if (!mysql_num_rows($rs)) {
	$sql = "DROP DATABASE " . $database;
	mysql_query($sql, $conn);
	createDatabase();
	mysql_close($conn);
	exit();
}


$sql = "SELECT val FROM db WHERE name = 'version'";
$rs = mysql_query($sql, $conn);
$val = current(mysql_fetch_row($rs));
if ($rs &&  $val != "0.3") {
	echo '<h2>Incorrect Database Version</h2>';
	echo "The database structure has been changed from your previous version. Upgrading database now. You should not access any other pages while this is is in progress.";
	echo "<pre>"; echo print_r($val); echo "</pre>";
	//migrateDatabase();
	mysql_close($conn);
	exit();
}

$sql = "SELECT val FROM db WHERE name = 'updated'";
$rs = mysql_query($sql, $conn);
if (!$rs) { 
	$sql = "INSERT INTO db (name, val) VALUES ('updated', 1)";
	quietSend($sql);
	$rs['val'] = 1;
} else {
	$rs = current(mysql_fetch_row($rs));
}
 
$warn = 24*60*60;
$now = time();
if ($now - $rs > $warn) {
	$title = 'Database data is out of date! ';
	$text = 'Your database has not been updated lately. You won\\\'t see anything new without updating.<BR /><a href="updateDatabase.php" target="_new">Click here to update it</a>.';
	$icon = "images/alert.png";
	notify($title, $text, $icon);
}



function notify($title, $msg, $icon) {
	echo '<script>';
	echo 'function create( template, vars, opts ){ return $container.notify("create", template, vars, opts); }';
	echo '$(function(){';
	echo '$container = $("#ncontainer").notify();';
	echo 'create("withIcon", { title: \'' . $title . '\', text: \'' . $msg . '\', icon:"' . $icon . '" },{ expires:false }); });';
	echo '</script>';
}

function createDatabase() {
	echo '<h2>Database Doesn\'t Exist</h2>';
	echo 'Creating your databse now. Please do not refresh your browser as this can take some time.';
	ob_flush();
$newUser = Vars::$dbuser;  //new user for access from php
$newPassword = Vars::$dbpwd; //new user's password
$database = Vars::$database;

	$conn = mysql_connect('localhost', "root", Vars::$rootpassword);
	if (!$conn) { die('Could not connect: ' . mysql_error()); }
	
	$sql = 'CREATE DATABASE ' . $database;
	if (mysql_query($sql, $conn)) {
		echo "Database my_db created successfully<br>\n";
	} else {
		echo 'Error creating database: ' . mysql_error() . "<BR>\n";
	}

	$sql = "CREATE USER '" . $newUser . "'@'localhost' IDENTIFIED BY '" . $newPassword . "'";
	if( mysql_query($sql, $conn) ) {
		echo "New user '$newUser' created succesfully.<BR>";
	} else {
		echo "New user '$newUser' could not be created" . mysql_error() . '<BR>';
	}

	$sql = "GRANT SELECT, INSERT, UPDATE, DELETE ON " . $database . ".* TO '" . $newUser . "'@'localhost'";
	if( mysql_query($sql, $conn) ) {
		echo "Privileges granted to '" . $newUser . "'<BR>";
	} else {
		echo "Privileges could not be granted to '" . $newUser . "'" . mysql_error() . '<BR>';
	}
	
	@mysql_select_db($database, $conn) or die( "Unable to select database" . mysql_error());
send("
CREATE TABLE IF NOT EXISTS `chars` (
  `name` varchar(13) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `wealth` int(11) NOT NULL,
  `server` tinyint(3) unsigned NOT NULL,
  `charID` smallint(3) unsigned NOT NULL AUTO_INCREMENT,
  UNIQUE KEY `charID` (`charID`),
  KEY `server` (`server`),
  KEY `server_2` (`server`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
", $conn);

send("
CREATE TABLE IF NOT EXISTS `completedauctions` (
  `stackCount` int(10) unsigned NOT NULL,
  `money` int(10) unsigned NOT NULL,
  `deposit` int(10) unsigned NOT NULL,
  `fee` int(10) unsigned NOT NULL,
  `buyout` int(10) unsigned NOT NULL,
  `bid` int(10) unsigned NOT NULL,
  `buyer` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `time` bigint(20) NOT NULL,
  `faction` tinyint(1) unsigned NOT NULL,
  `server` int(10) unsigned NOT NULL,
  `charID` int(10) unsigned NOT NULL,
  `itemID` int(11) NOT NULL,
  `auctionID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`auctionID`),
  KEY `server` (`server`),
  KEY `itemID` (`itemID`),
  KEY `time` (`time`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
", $conn);

send("
CREATE TABLE IF NOT EXISTS `completedbuyouts` (
  `stackCount` tinyint(2) NOT NULL,
  `price` int(11) NOT NULL,
  `seller` varchar(13) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `time` int(11) NOT NULL,
  `itemID` int(10) unsigned NOT NULL,
  `charid` int(10) unsigned NOT NULL,
  `serverID` mediumint(8) unsigned NOT NULL,
  `buyoutID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`buyoutID`),
  KEY `charid` (`charid`),
  KEY `itemID` (`itemID`),
  KEY `time` (`time`),
  KEY `serverID` (`serverID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
", $conn);

send("
CREATE TABLE IF NOT EXISTS `crafting` (
  `spellName` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `learnedat` smallint(4) NOT NULL,
  `orange` smallint(6) NOT NULL,
  `yellow` smallint(6) NOT NULL,
  `green` smallint(6) NOT NULL,
  `gray` smallint(6) NOT NULL,
  `productID` int(11) NOT NULL,
  `output` tinyint(2) unsigned NOT NULL,
  `search` tinyint(1) NOT NULL DEFAULT '1',
  `cd` tinyint(4) NOT NULL DEFAULT '0',
  `spellid` int(11) unsigned DEFAULT NULL,
  `profID` tinyint(2) unsigned NOT NULL,
  `craftID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`craftID`),
  KEY `profID` (`profID`),
  KEY `spellid` (`spellid`),
  KEY `productID` (`productID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
", $conn);

send("
CREATE TABLE IF NOT EXISTS `craftinginputs` (
  `inputID` int(10) unsigned NOT NULL,
  `inputCount` int(10) unsigned NOT NULL,
  `craftID` int(10) unsigned NOT NULL,
  KEY `inputID` (`inputID`),
  KEY `craftID` (`craftID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
", $conn);

send("
CREATE TABLE IF NOT EXISTS `craftingskills` (
  `charid` smallint(5) unsigned NOT NULL,
  `craftid` int(10) unsigned NOT NULL,
  KEY `craftid` (`craftid`),
  KEY `charid` (`charid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
", $conn);

send("
CREATE TABLE IF NOT EXISTS `db` (
  `name` varchar(20) COLLATE utf8_bin NOT NULL,
  `val` varchar(30) COLLATE utf8_bin NOT NULL,
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
", $conn);

	send("INSERT INTO db (name, val) VALUES ('version', '0.3')",$conn);
	send("INSERT INTO db (name, val) VALUES ('updated', '1')",$conn);
	
send("
CREATE TABLE IF NOT EXISTS `items` (
  `name` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `price` int(10) unsigned NOT NULL,
  `suffix` int(11) NOT NULL,
  `quality` tinyint(1) NOT NULL,
  `icon` text NOT NULL,
  `itemID` int(11) NOT NULL,
  PRIMARY KEY (`itemID`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
", $conn);

send("
CREATE TABLE IF NOT EXISTS `profession` (
  `name` varchar(14) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `icon` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
", $conn);

	send("INSERT INTO `profession` (`name`, `icon`, `id`) VALUES('Alchemy', 'trade_alchemy.png', 1)",$conn);
	send("INSERT INTO `profession` (`name`, `icon`, `id`) VALUES('Blacksmithing', 'trade_blacksmithing.png', 2)",$conn);
	send("INSERT INTO `profession` (`name`, `icon`, `id`) VALUES('Cooking', 'inv_misc_food_15.png', 3)",$conn);
	send("INSERT INTO `profession` (`name`, `icon`, `id`) VALUES('Enchanting', 'trade_engraving.png', 4)",$conn);
	send("INSERT INTO `profession` (`name`, `icon`, `id`) VALUES('Engineering', 'trade_engineering.png', 5)",$conn);
	send("INSERT INTO `profession` (`name`, `icon`, `id`) VALUES('General', 'temp.png', 6)",$conn);
	send("INSERT INTO `profession` (`name`, `icon`, `id`) VALUES('Inscription', 'inv_inscription_tradeskill01.png', 7)",$conn);
	send("INSERT INTO `profession` (`name`, `icon`, `id`) VALUES('Jewelcrafting', 'inv_misc_gem_01.png', 8)",$conn);
	send("INSERT INTO `profession` (`name`, `icon`, `id`) VALUES('Leatherworking', 'trade_leatherworking.png', 9)",$conn);
	send("INSERT INTO `profession` (`name`, `icon`, `id`) VALUES('Mining', 'trade_mining.png', 10)",$conn);
	send("INSERT INTO `profession` (`name`, `icon`, `id`) VALUES('Tailoring', 'trade_tailoring.png', 11)",$conn);
	
send("
CREATE TABLE IF NOT EXISTS `realms` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
", $conn);

send("
CREATE TABLE IF NOT EXISTS `scans` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `itemid` smallint(5) unsigned NOT NULL,
  `itemsuffix` smallint(5) NOT NULL,
  `itemenchant` smallint(5) NOT NULL,
  `itemfactor` tinyint(3) NOT NULL,
  `itemseed` int(12) NOT NULL,
  `stacksize` tinyint(2) unsigned NOT NULL,
  `sellername` varchar(13) NOT NULL,
  `minbid` int(12) NOT NULL,
  `buyoutprice` int(12) NOT NULL,
  `curbid` int(12) NOT NULL,
  `timeleft` tinyint(3) unsigned NOT NULL,
  `realm` tinyint(3) unsigned NOT NULL,
  `scanid` smallint(5) unsigned NOT NULL,
  `timestamp` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `itemid` (`itemid`),
  KEY `sellername` (`sellername`),
  KEY `timedstamp` (`timestamp`),
  KEY `realm` (`realm`),
  KEY `buyoutprice` (`buyoutprice`),
  KEY `stacksize` (`stacksize`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
", $conn);

send("
CREATE TABLE IF NOT EXISTS `scansummary` (
  `seen` mediumint(9) NOT NULL,
  `total` int(11) NOT NULL,
  `min` int(10) NOT NULL,
  `max` int(10) NOT NULL,
  `avg` int(11) NOT NULL,
  `stddev` mediumint(9) NOT NULL,
  `moving` int(11) NOT NULL,
  `date` date NOT NULL,
  `itemid` int(11) NOT NULL,
  KEY `date` (`date`),
  KEY `itemid` (`itemid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
", $conn);

	$dumpfile = substr($_SERVER['SCRIPT_FILENAME'], 0, strpos($_SERVER['SCRIPT_FILENAME'], "www")) . 'inc/items.txt';
send("
LOAD DATA INFILE '$dumpfile' INTO TABLE items
 FIELDS TERMINATED BY ';'
", $conn);

	$dumpfile = substr($_SERVER['SCRIPT_FILENAME'], 0, strpos($_SERVER['SCRIPT_FILENAME'], "www")) . 'inc/crafting.txt';
send("
LOAD DATA INFILE '$dumpfile' INTO TABLE crafting
 FIELDS TERMINATED BY ';'
", $conn);

	$dumpfile = substr($_SERVER['SCRIPT_FILENAME'], 0, strpos($_SERVER['SCRIPT_FILENAME'], "www")) . 'inc/craftinginputs.txt';
send("
LOAD DATA INFILE '$dumpfile' INTO TABLE craftinginputs
 FIELDS TERMINATED BY ';'
", $conn);

echo "Database creation complete. Refresh to start using Campfire.";
}


function migrateDatabase() {
	migrateTo0x2();
}

function migrateTo0x2() {
}


function quietSend($sql, $conn) {
	send ($sql, $conn, false);
}

function send($sql,$conn, $debug = true) {
	echo "Current Statement: $sql ... ";
	$check = mysql_query($sql, $conn);
	if ($check) {
		if ($debug) { echo "Success.<br />"; }
		return true;
	} else {
		if ($debug) { echo "Failed.<br />"; }
		echo  mysql_error() . "<br /";
		return false;
	} 
}

?>